package dao.impl;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import boerrors.EmployeExistantException;
import boerrors.EmployeInexistantException;
import dao.EmployeDao;
import dao.dto.AdresseDto;
import dao.dto.CodePostalDto;
import dao.dto.ZoneDto;
import dao.dto.RoleDto;
import dao.dto.EmployeDto;

public class EmployeDaoImpl extends EmployeDao{
    private static EmployeDao _instance = null;
    private CallableStatement _callableStatement = null;
    private SQLConnection _connection = new SQLConnection();

    //Constructeur du singleton
    public static EmployeDao getInstance(){
            if(_instance == null)
                    _instance = new EmployeDaoImpl();
            return _instance;
    }

    @Override
    public void enregistrer(EmployeDto employe) throws EmployeExistantException {
            try {
                    //query
                    _callableStatement = _connection.getConnection().prepareCall("{call sp_RHEmployeInsert(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
                    //parameter
                    _callableStatement.setInt("idRHMatricule",employe.getMatricule());
                    _callableStatement.setString("nom",employe.getNom());
                    _callableStatement.setString("prenom",employe.getPrenom());
                    _callableStatement.setString("telephone",employe.getTelephone());
                    _callableStatement.setString("email",employe.getEmail());
                    _callableStatement.setString("rue",employe.getAdresse().getRue());
                    _callableStatement.setString("numero",employe.getAdresse().getNumero());
                    _callableStatement.setInt("codePostalFK",employe.getAdresse().getCodePostal().getIdCodePostal());
                    _callableStatement.setString("fonction",employe.getFonction());                        
                    _callableStatement.setString("password",employe.getPassword());
                    _callableStatement.setBoolean("gestionClient",employe.getRole().getGestionClient());
                    _callableStatement.setBoolean("gestionDirection",employe.getRole().getGestionDirection());
                    _callableStatement.setBoolean("gestionIntervention",employe.getRole().getGestionIntervention());
                    _callableStatement.setBoolean("gestionOnSite",employe.getRole().getGestionOnSite());
                    _callableStatement.setBoolean("gestionPlanning",employe.getRole().getGestionPlanning());
                    _callableStatement.setBoolean("gestionSysteme",employe.getRole().getGestionSysteme());

                    //execute
                    _callableStatement.executeUpdate(); 
        } catch(SQLException e) {
            if (e.getMessage().equals("547")) {
                throw new EmployeExistantException("Code postal/Adresse/Role Incorrect (FK)",e);
            } else if (e.getMessage().equals("515")) {
                throw new EmployeExistantException("Non autorisé champs vide",e);
            } else if (e.getMessage().equals("2627")) {
                throw new EmployeExistantException("Le matricule existe déjà",e);
            } else if (e.getMessage().equals("50001")) {
                throw new EmployeExistantException("Un employé ne peut avoir le droit gestionOnsite",e);
            } else  throw new EmployeExistantException(e);
        } catch(Exception e){
            throw new EmployeExistantException(e);
        } finally {
                if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {throw new EmployeExistantException(e);}
                _connection.deconnecter();
        }
    }
        
    @Override
    public void modifier(EmployeDto employe) throws EmployeInexistantException {
            try {
                    //query
                    _callableStatement = _connection.getConnection().prepareCall("{call sp_RHEmployeUpdate(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
                    //parameter
                    
                    _callableStatement.setInt("idRHMatricule",employe.getMatricule());
                    _callableStatement.setString("nom",employe.getNom());
                    _callableStatement.setString("prenom",employe.getPrenom());
                    _callableStatement.setString("telephone",employe.getTelephone());
                    _callableStatement.setString("email",employe.getEmail());
                    //_callableStatement.setInt("idAdresse",employe.getAdresse().getIdAdresse());
                    _callableStatement.setString("rue",employe.getAdresse().getRue());
                    _callableStatement.setString("numero",employe.getAdresse().getNumero());
                    _callableStatement.setInt("codePostalFK",employe.getAdresse().getCodePostal().getIdCodePostal());
                    _callableStatement.setString("fonction",employe.getFonction());                        
                    _callableStatement.setString("password",employe.getPassword());
                    //_callableStatement.setInt("idRoles",employe.getRole().getIdRole());
                    _callableStatement.setBoolean("gestionClient",employe.getRole().getGestionClient());
                    _callableStatement.setBoolean("gestionDirection",employe.getRole().getGestionDirection());
                    _callableStatement.setBoolean("gestionIntervention",employe.getRole().getGestionIntervention());
                    _callableStatement.setBoolean("gestionOnSite",employe.getRole().getGestionOnSite());
                    _callableStatement.setBoolean("gestionPlanning",employe.getRole().getGestionPlanning());
                    _callableStatement.setBoolean("gestionSysteme",employe.getRole().getGestionSysteme());
                
                    //execute
                    _callableStatement.executeUpdate();    

        } catch(SQLException e) {
            if (e.getMessage().equals("547")) {
                throw new EmployeInexistantException("Code postal/Adresse/Role Incorrect",e);
            } else if (e.getMessage().equals("515")) {
                throw new EmployeInexistantException("Non autorisé champs vide",e);
            } else if (e.getMessage().equals("50001")) {
                    throw new EmployeInexistantException("L'employé n'existe pas",e);
            } else if (e.getMessage().equals("50002")) {
                    throw new EmployeInexistantException("L'adresse n'a pas été trouvé",e);
            } else if (e.getMessage().equals("50003")) {
                    throw new EmployeInexistantException("Le rôle n'a pas été trouvé",e);
            } else if (e.getMessage().equals("50004")) {
                throw new EmployeInexistantException("Un employé ne peut avoir le droit gestionOnsite",e);
            } else  throw new EmployeInexistantException(e);
        } catch(Exception e){
            throw new EmployeInexistantException(e);
        } finally {
                if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {throw new EmployeInexistantException(e);}
                _connection.deconnecter();
        }
    }
     
    @Override
    public void modifierTechnicienToEmploye(EmployeDto employe) throws EmployeInexistantException {
            try {
                    //query
                    _callableStatement = _connection.getConnection().prepareCall("{call sp_RHTechnicienToEmployeUpdate(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
                    //parameter
                    _callableStatement.setInt("idRHMatricule",employe.getMatricule());
                    _callableStatement.setString("nom",employe.getNom());
                    _callableStatement.setString("prenom",employe.getPrenom());
                    _callableStatement.setString("telephone",employe.getTelephone());
                    _callableStatement.setString("email",employe.getEmail());
                    //_callableStatement.setInt("idAdresse",employe.getAdresse().getIdAdresse());
                    _callableStatement.setString("rue",employe.getAdresse().getRue());
                    _callableStatement.setString("numero",employe.getAdresse().getNumero());
                    _callableStatement.setInt("codePostalFK",employe.getAdresse().getCodePostal().getIdCodePostal());
                    _callableStatement.setString("fonction",employe.getFonction());                        
                    _callableStatement.setString("password",employe.getPassword());
                    //_callableStatement.setInt("idRoles",employe.getRole().getIdRole());
                    _callableStatement.setBoolean("gestionClient",employe.getRole().getGestionClient());
                    _callableStatement.setBoolean("gestionDirection",employe.getRole().getGestionDirection());
                    _callableStatement.setBoolean("gestionIntervention",employe.getRole().getGestionIntervention());
                    _callableStatement.setBoolean("gestionOnSite",employe.getRole().getGestionOnSite());
                    _callableStatement.setBoolean("gestionPlanning",employe.getRole().getGestionPlanning());
                    _callableStatement.setBoolean("gestionSysteme",employe.getRole().getGestionSysteme());
                
                    //execute
                    _callableStatement.executeUpdate();    

        } catch(SQLException e) {
            if (e.getMessage().equals("547")) {
                throw new EmployeInexistantException("Code postal/Adresse/Role Incorrect",e);
            } else if (e.getMessage().equals("515")) {
                throw new EmployeInexistantException("Non autorisé champs vide",e);
            } else if (e.getMessage().equals("50001")) {
                    throw new EmployeInexistantException("Le technicien n'existe pas",e);
            } else if (e.getMessage().equals("50002")) {
                    throw new EmployeInexistantException("L'adresse n'a pas été trouvé",e);
            } else if (e.getMessage().equals("50003")) {
                    throw new EmployeInexistantException("Le rôle n'a pas été trouvé",e);
            } else if (e.getMessage().equals("50004")) {
                throw new EmployeInexistantException("Un employé ne peut avoir le droit gestionOnsite",e);
            } else  throw new EmployeInexistantException(e);
        } catch(Exception e){
            throw new EmployeInexistantException(e);
        } finally {
                if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {throw new EmployeInexistantException(e);}
                _connection.deconnecter();
        }
    }

    @Override
    public Vector<EmployeDto> lister() throws EmployeInexistantException {
        ResultSet resultat = null;
        RoleDto role = null;
        ZoneDto zone = null;
        CodePostalDto codePostal = null;
        AdresseDto adresse = null;
        EmployeDto employe = null;
            try {                                      
                //query
                _callableStatement = _connection.getConnection().prepareCall("{call sp_RHEmployeList}");    
                //execute
                resultat = _callableStatement.executeQuery();
                
                Vector<EmployeDto> liste = new Vector<EmployeDto>();
                while (resultat.next()) {
                    role = new RoleDto( resultat.getInt("idRoles")
                                        ,resultat.getBoolean("gestionClient"),resultat.getBoolean("gestionDirection")
                                        ,resultat.getBoolean("gestionIntervention"), resultat.getBoolean("gestionOnSite")
                                        ,resultat.getBoolean("gestionPlanning"), resultat.getBoolean("gestionSysteme"));
                    zone = new ZoneDto( resultat.getInt("idZone"),
                                        resultat.getString("libZone"));
                    codePostal = new CodePostalDto( resultat.getInt("idCodePostal"),
                                                    resultat.getString("cp"),
                                                    resultat.getString("ville"),
                                                    resultat.getString("libPays"),
                                                    zone);
                    adresse = new AdresseDto(   resultat.getInt("idAdresse"),
                                                resultat.getString("rue"),
                                                resultat.getString("numero"),
                                                codePostal
                                                );
                    employe = new EmployeDto(  resultat.getInt("idRHMatricule")
                                                        ,resultat.getString("nom")
                                                        ,resultat.getString("prenom")
                                                        ,adresse
                                                        ,resultat.getString("telephone")
                                                        ,resultat.getString("email")
                                                        ,resultat.getString("fonction")
                                                        ,resultat.getString("password")
                                                        ,role);
                    
                    liste.add(employe);
                }
                return liste;
            } catch(SQLException e) { 
                throw new EmployeInexistantException(e);
            } finally { 
                if (resultat != null) try { resultat.close(); } catch(Exception e) { throw new EmployeInexistantException(e);}
                if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {throw new EmployeInexistantException(e);}
                    _connection.deconnecter();
            }
    }    
    
    @Override
    public EmployeDto identifier(int idRHMatricule,String password) throws EmployeInexistantException {
        ResultSet resultat = null;
        RoleDto role = null;
        ZoneDto zone = null;
        CodePostalDto codePostal = null;
        AdresseDto adresse = null;
        EmployeDto employe = null;
        
            try {                                      
                //query
                _callableStatement = _connection.getConnection().prepareCall("{call sp_RHEmployeIdentify(?,?)}");    
                //parameter
                _callableStatement.setInt("idRHMatricule",idRHMatricule);
                _callableStatement.setString("password",password);                
                //execute
                resultat = _callableStatement.executeQuery();
                
                if (resultat.next()) {
                    role = new RoleDto( resultat.getInt("idRoles")
                                        ,resultat.getBoolean("gestionClient"),resultat.getBoolean("gestionDirection")
                                        ,resultat.getBoolean("gestionIntervention"), resultat.getBoolean("gestionOnSite")
                                        ,resultat.getBoolean("gestionPlanning"), resultat.getBoolean("gestionSysteme"));
                    zone = new ZoneDto( resultat.getInt("idZone"),
                                        resultat.getString("libZone"));
                    codePostal = new CodePostalDto( resultat.getInt("idCodePostal"),
                                                    resultat.getString("cp"),
                                                    resultat.getString("ville"),
                                                    resultat.getString("libPays"),
                                                    zone);
                    adresse = new AdresseDto(   resultat.getInt("idAdresse"),
                                                resultat.getString("rue"),
                                                resultat.getString("numero"),
                                                codePostal
                                                );
                    employe = new EmployeDto(  resultat.getInt("idRHMatricule")
                                                        ,resultat.getString("nom")
                                                        ,resultat.getString("prenom")
                                                        ,adresse
                                                        ,resultat.getString("telephone")
                                                        ,resultat.getString("email")
                                                        ,resultat.getString("fonction")
                                                        ,resultat.getString("password")
                                                        ,role);            
                }
                return employe;
            } catch(SQLException e) { 
                throw new EmployeInexistantException(e);
            } finally { 
                if (resultat != null) try { resultat.close(); } catch(Exception e) { throw new EmployeInexistantException(e);}
                if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {throw new EmployeInexistantException(e);}
                    _connection.deconnecter();
            }
}
        /*
        //Methodes
	@Override
	public EmployeDto rechercher(String matricule) throws EmployeInexistantException {
                ResultSet resultat = null;
                ZoneDto zone = null;
                AdresseDto adresse = null;
                RoleDto role = null;
                try {
                        //query
                        _callableStatement = _connection.getConnection().prepareCall("{call sp_EmployeSelectMatricule(?)}");
                        //parameter
                        _callableStatement.setString("matricule",matricule);
                        //execute
                        resultat = _callableStatement.executeQuery();    
                	if(!resultat.next()) throw new EmployeInexistantException();
			zone = new ZoneDto(resultat.getString("ZONE"));
                         adresse = new AdresseDto(   resultat.getString("RUE"),
                                                    resultat.getString("NUMERO"),
                                                    resultat.getString("CODEPOSTAL"),
                                                    resultat.getString("VILLE"),
                                                    resultat.getString("PAYS"),
                                                    zone);
                        role = new RoleDto(     resultat.getLong("IDROLE"),
                                                resultat.getBoolean("GESTIONCLIENT"),
                                                resultat.getBoolean("GESTIONINTERVENTION"),
                                                resultat.getBoolean("GESTIONPLANNING"),
                                                resultat.getBoolean("GESTIONDIRECTION"),
                                                resultat.getBoolean("GESTIONONSITE"),
                                                resultat.getBoolean("GESTIONSYSTEME"));
                        return new EmployeDto(  resultat.getString("MATRICULE"),
                                                resultat.getString("NOM"),
                                                resultat.getString("PRENOM"),
                                                adresse,
                                                resultat.getString("TELEPHONE"),
                                                resultat.getString("EMAIL"),
                                                role, 
                                                resultat.getString("PASSWORD")); 
		}catch(SQLException e){throw new EmployeInexistantException(e);
                }finally{           
                        if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {}
                        _connection.deconnecter();}
	}

        @Override
        public Vector<EmployeDto> rechercher(String nom, String prenom) throws EmployeInexistantException {
		ResultSet resultat = null;
                ZoneDto zone = null;
                AdresseDto adresse = null;
                RoleDto role = null;
                EmployeDto employe = null;
                try {
                        //query
                        _callableStatement = _connection.getConnection().prepareCall("{call sp_EmployeSelectNomPrenom(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
                        //parameter
                        _callableStatement.setString("nom",nom);
                        _callableStatement.setString("prenom",prenom);
                        //execute
                        resultat = _callableStatement.executeQuery();    
                	if(!resultat.next()) throw new EmployeInexistantException();
			Vector<EmployeDto> liste = new Vector<EmployeDto>();
			while(resultat.next()){
                                zone = new ZoneDto(resultat.getString("ZONE"));
                                adresse = new AdresseDto(   resultat.getString("RUE"),
                                                            resultat.getString("NUMERO"),
                                                            resultat.getString("CODEPOSTAL"),
                                                            resultat.getString("VILLE"),
                                                            resultat.getString("PAYS"),
                                                            zone);
                                role = new RoleDto(     resultat.getLong("IDROLE"),
                                                        resultat.getBoolean("GESTIONCLIENT"),
                                                        resultat.getBoolean("GESTIONINTERVENTION"),
                                                        resultat.getBoolean("GESTIONPLANNING"),
                                                        resultat.getBoolean("GESTIONDIRECTION"),
                                                        resultat.getBoolean("GESTIONONSITE"),
                                                        resultat.getBoolean("GESTIONSYSTEME"));
                                employe = new EmployeDto(   resultat.getString("MATRICULE"),
                                                            resultat.getString("NOM"),
                                                            resultat.getString("PRENOM"),
                                                            adresse,
                                                            resultat.getString("TELEPHONE"),
                                                            resultat.getString("EMAIL"),
                                                            role, 
                                                            resultat.getString("PASSWORD"));
                                liste.add(employe);}
                        return liste;
		}catch(SQLException e){throw new EmployeInexistantException(e);
                }finally{           
                        if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {}
                        _connection.deconnecter();}
	}
                       
        
        
        @Override
	public void modifier(EmployeDto employe) throws EmployeInexistantException {
		try {
                        //query
                        _callableStatement = _connection.getConnection().prepareCall("{call sp_EmployeUpdate(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
                        //parameter
                        _callableStatement.setString("nom",employe.getNom());
                        _callableStatement.setString("prenom",employe.getPrenom());
                        _callableStatement.setString("rue",employe.getAdresse().getRue());
                        _callableStatement.setString("numero",employe.getAdresse().getNumero());
                   //     _callableStatement.setString("codePostal",employe.getAdresse().getCodePostal());
                     //   _callableStatement.setString("ville",employe.getAdresse().getVille());
                       // _callableStatement.setString("pays",employe.getAdresse().getPays());
                       // _callableStatement.setString("zone",employe.getAdresse().getZone().getLibelleZone());
                        _callableStatement.setString("telephone",employe.getTelephone());
                        _callableStatement.setString("email",employe.getEmail());
                        _callableStatement.setLong("idRole",employe.getRole().getIdRole());
                        _callableStatement.setBoolean("gestionClient",employe.getRole().getGestionClient());
                        _callableStatement.setBoolean("gestionIntervention",employe.getRole().getGestionClient());
                        _callableStatement.setBoolean("gestionPlanning",employe.getRole().getGestionClient());
                        _callableStatement.setBoolean("gestionDirection",employe.getRole().getGestionClient());
                        _callableStatement.setBoolean("gestionOnSite",employe.getRole().getGestionClient());
                        _callableStatement.setBoolean("gestionSysteme",employe.getRole().getGestionClient());
                        _callableStatement.setString("password",employe.getPassword());
                        //execute
                        _callableStatement.executeUpdate();    
                }catch(SQLException e){throw new EmployeInexistantException(e);
                }finally{
                        if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {}
                        _connection.deconnecter();}
        }

	@Override
	public void supprimer(EmployeDto employe) throws EmployeInexistantException {
		try {
                        //query
                        _callableStatement = _connection.getConnection().prepareCall("{call sp_EmployeDelete(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
                        //parameter
                        _callableStatement.setString("nom",employe.getNom());
                        _callableStatement.setString("prenom",employe.getPrenom());
                        _callableStatement.setString("rue",employe.getAdresse().getRue());
                        _callableStatement.setString("numero",employe.getAdresse().getNumero());
                        //_callableStatement.setString("codePostal",employe.getAdresse().getCodePostal());
                        //_callableStatement.setString("ville",employe.getAdresse().getVille());
                        //_callableStatement.setString("pays",employe.getAdresse().getPays());
                        //_callableStatement.setString("zone",employe.getAdresse().getZone().getLibelleZone());
                        _callableStatement.setString("telephone",employe.getTelephone());
                        _callableStatement.setString("email",employe.getEmail());
                        _callableStatement.setLong("idRole",employe.getRole().getIdRole());
                        _callableStatement.setBoolean("gestionClient",employe.getRole().getGestionClient());
                        _callableStatement.setBoolean("gestionIntervention",employe.getRole().getGestionClient());
                        _callableStatement.setBoolean("gestionPlanning",employe.getRole().getGestionClient());
                        _callableStatement.setBoolean("gestionDirection",employe.getRole().getGestionClient());
                        _callableStatement.setBoolean("gestionOnSite",employe.getRole().getGestionClient());
                        _callableStatement.setBoolean("gestionSysteme",employe.getRole().getGestionClient());
                        _callableStatement.setString("password",employe.getPassword());
                        //execute
                        _callableStatement.executeUpdate();    
                }catch(SQLException e){throw new EmployeInexistantException(e);
                }finally{
                        if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {}
                        _connection.deconnecter();}
        } */
}